package cn.iocoder.yudao.module.system.dal.mysql.examscore;

import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.module.system.dal.dataobject.examscore.ExamScoreDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * Teacher Dashboard Performance Optimized ExamScoreMapper
 * Provides batch query and optimized statistical methods
 *
 * @author YuDao
 */
@Mapper
public interface ExamScoreMapperOptimized extends BaseMapperX<ExamScoreDO> {

    // ==================== Batch Query Optimization Methods ====================

    /**
     * Batch get all student scores for exam (one-time query, avoid N+1 problem)
     */
    @Select("SELECT * FROM system_exam_score WHERE exam_id = #{examId} ORDER BY total_score DESC")
    List<ExamScoreDO> selectAllScoresByExamId(@Param("examId") Long examId);

    /**
     * Batch get student scores for multiple exams
     */
    @Select("SELECT * FROM system_exam_score WHERE exam_id IN (${examIds}) ORDER BY exam_id, total_score DESC")
    List<ExamScoreDO> selectScoresByExamIds(@Param("examIds") String examIds);

    /**
     * Batch get class student scores
     */
    @Select("SELECT * FROM system_exam_score " +
            "WHERE exam_id = #{examId} AND class_id = #{classId} " +
            "ORDER BY total_score DESC")
    List<ExamScoreDO> selectClassScoresByExamId(@Param("examId") Long examId, @Param("classId") Long classId);

    /**
     * Batch get student scores for multiple classes
     */
    @Select("SELECT * FROM system_exam_score WHERE exam_id = #{examId} AND class_id IN (${classIds}) ORDER BY class_id, total_score DESC")
    List<ExamScoreDO> selectMultiClassScores(@Param("examId") Long examId, @Param("classIds") String classIds);

    // ==================== Statistical Query Optimization Methods ====================

    /**
     * Batch get exam statistics (including average scores for all subjects)
     */
    @Select("SELECT " +
            "COUNT(*) as totalStudents, " +
            "AVG(total_score) as avgScore, " +
            "MAX(total_score) as maxScore, " +
            "MIN(total_score) as minScore, " +
            "STDDEV(total_score) as standardDeviation, " +
            "AVG(chinese_score) as avgChineseScore, " +
            "AVG(math_score) as avgMathScore, " +
            "AVG(english_score) as avgEnglishScore, " +
            "AVG(physics_score) as avgPhysicsScore, " +
            "AVG(chemistry_score) as avgChemistryScore, " +
            "AVG(biology_score) as avgBiologyScore, " +
            "AVG(politics_score) as avgPoliticsScore, " +
            "AVG(history_score) as avgHistoryScore, " +
            "AVG(geography_score) as avgGeographyScore, " +
            "SUM(CASE WHEN total_score >= 680 THEN 1 ELSE 0 END) as count985, " +
            "SUM(CASE WHEN total_score >= 650 THEN 1 ELSE 0 END) as count211, " +
            "SUM(CASE WHEN total_score >= 620 THEN 1 ELSE 0 END) as countFirstTier " +
            "FROM system_exam_score WHERE exam_id = #{examId}")
    Map<String, Object> selectBatchStatistics(@Param("examId") Long examId);

    /**
     * Batch get class statistics
     */
    @Select("SELECT " +
            "class_id, " +
            "COUNT(*) as totalStudents, " +
            "AVG(total_score) as avgScore, " +
            "MAX(total_score) as maxScore, " +
            "MIN(total_score) as minScore, " +
            "STDDEV(total_score) as standardDeviation, " +
            "SUM(CASE WHEN total_score >= 680 THEN 1 ELSE 0 END) as count985, " +
            "SUM(CASE WHEN total_score >= 650 THEN 1 ELSE 0 END) as count211, " +
            "SUM(CASE WHEN total_score >= 620 THEN 1 ELSE 0 END) as countFirstTier " +
            "FROM system_exam_score " +
            "WHERE exam_id = #{examId} " +
            "GROUP BY class_id")
    List<Map<String, Object>> selectBatchClassStatistics(@Param("examId") Long examId);

    /**
     * Batch get subject score statistics (one query gets all subjects)
     */
    @Select("SELECT " +
            "'Chinese' as subjectName, AVG(chinese_score) as avgScore, MAX(chinese_score) as maxScore, MIN(chinese_score) as minScore, " +
            "SUM(CASE WHEN chinese_score >= 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN chinese_score >= 120 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND chinese_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT " +
            "'Math' as subjectName, AVG(math_score) as avgScore, MAX(math_score) as maxScore, MIN(math_score) as minScore, " +
            "SUM(CASE WHEN math_score >= 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN math_score >= 120 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND math_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT " +
            "'English' as subjectName, AVG(english_score) as avgScore, MAX(english_score) as maxScore, MIN(english_score) as minScore, " +
            "SUM(CASE WHEN english_score >= 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN english_score >= 120 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND english_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT " +
            "'Physics' as subjectName, AVG(physics_score) as avgScore, MAX(physics_score) as maxScore, MIN(physics_score) as minScore, " +
            "SUM(CASE WHEN physics_score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN physics_score >= 85 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND physics_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT " +
            "'Chemistry' as subjectName, AVG(chemistry_score) as avgScore, MAX(chemistry_score) as maxScore, MIN(chemistry_score) as minScore, " +
            "SUM(CASE WHEN chemistry_score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN chemistry_score >= 85 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND chemistry_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT " +
            "'Biology' as subjectName, AVG(biology_score) as avgScore, MAX(biology_score) as maxScore, MIN(biology_score) as minScore, " +
            "SUM(CASE WHEN biology_score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN biology_score >= 85 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND biology_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT " +
            "'Politics' as subjectName, AVG(politics_score) as avgScore, MAX(politics_score) as maxScore, MIN(politics_score) as minScore, " +
            "SUM(CASE WHEN politics_score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN politics_score >= 85 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND politics_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT " +
            "'History' as subjectName, AVG(history_score) as avgScore, MAX(history_score) as maxScore, MIN(history_score) as minScore, " +
            "SUM(CASE WHEN history_score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN history_score >= 85 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND history_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT " +
            "'Geography' as subjectName, AVG(geography_score) as avgScore, MAX(geography_score) as maxScore, MIN(geography_score) as minScore, " +
            "SUM(CASE WHEN geography_score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN geography_score >= 85 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score WHERE exam_id = #{examId} AND geography_score IS NOT NULL")
    List<Map<String, Object>> selectBatchSubjectStatistics(@Param("examId") Long examId);

    // ==================== Ranking Query Optimization Methods ====================

    /**
     * Get student rankings with pagination (avoid loading all students at once)
     */
    @Select("SELECT s.student_id, s.student_name, s.student_no, s.class_name, s.total_score, s.class_rank, s.grade_rank, " +
            "s.subject_combination, s.first_subject, s.second_subject, s.exam_type, " +
            "s.total_score_assigned, s.combination_rank, s.school_rank, s.total_union_rank " +
            "FROM system_exam_score s " +
            "WHERE s.exam_id = #{examId} " +
            "ORDER BY s.total_score DESC " +
            "LIMIT #{offset}, #{limit}")
    List<Map<String, Object>> selectStudentRankingByPage(@Param("examId") Long examId,
                                                         @Param("offset") Integer offset,
                                                         @Param("limit") Integer limit);

    /**
     * Get top N students in class (optimized version)
     */
    @Select("SELECT s.student_id, s.student_name, s.student_no, s.class_name, s.total_score, s.class_rank, s.grade_rank, " +
            "s.subject_combination, s.first_subject, s.second_subject, s.exam_type, " +
            "s.total_score_assigned, s.combination_rank, s.school_rank, s.total_union_rank, " +
            "CASE " +
            "WHEN s.total_score >= 680 THEN 'Top985' " +
            "WHEN s.total_score >= 650 THEN 'Top211' " +
            "WHEN s.total_score >= 620 THEN 'FirstTier' " +
            "WHEN s.total_score >= 550 THEN 'SecondTier' " +
            "ELSE 'Specialty' " +
            "END as predictedAdmissionLevel " +
            "FROM system_exam_score s " +
            "WHERE s.class_id = #{classId} AND s.exam_id = #{examId} " +
            "ORDER BY s.total_score DESC " +
            "LIMIT #{topN}")
    List<Map<String, Object>> selectOptimizedClassTopStudents(@Param("classId") Long classId,
                                                              @Param("examId") Long examId,
                                                              @Param("topN") Integer topN);

    /**
     * Batch get top N students from multiple classes
     */
    @Select("SELECT s.class_id, s.student_id, s.student_name, s.student_no, s.class_name, s.total_score, s.class_rank, s.grade_rank " +
            "FROM system_exam_score s " +
            "WHERE s.exam_id = #{examId} AND s.class_id IN (${classIds}) " +
            "AND s.class_rank <= #{topN} " +
            "ORDER BY s.class_id, s.total_score DESC")
    List<Map<String, Object>> selectMultiClassTopStudents(@Param("examId") Long examId,
                                                          @Param("classIds") String classIds,
                                                          @Param("topN") Integer topN);

    // ==================== Comparison Analysis Optimization Methods ====================

    /**
     * Batch get student historical score comparison (optimized version)
     */
    @Select("SELECT s1.student_id, s1.student_name, s1.student_no, " +
            "s1.total_score as currentScore, s2.total_score as previousScore, " +
            "(s1.total_score - s2.total_score) as progressScore, " +
            "(s2.class_rank - s1.class_rank) as rankImprovement, " +
            "s1.class_rank as currentRank, s2.class_rank as previousRank " +
            "FROM system_exam_score s1 " +
            "LEFT JOIN system_exam_score s2 ON s1.student_id = s2.student_id " +
            "WHERE s1.exam_id = #{currentExamId} AND s2.exam_id = #{previousExamId} " +
            "ORDER BY (s1.total_score - s2.total_score) DESC")
    List<Map<String, Object>> selectBatchStudentProgress(@Param("currentExamId") Long currentExamId,
                                                         @Param("previousExamId") Long previousExamId);

    /**
     * Batch get students needing attention (declining scores or low rankings)
     */
    @Select("SELECT s1.student_id, s1.student_name, s1.student_no, " +
            "s1.total_score as currentScore, s2.total_score as previousScore, " +
            "(s1.total_score - s2.total_score) as scoreChange, " +
            "(s1.class_rank - s2.class_rank) as rankChange, " +
            "s1.subject_combination, s1.first_subject, s1.second_subject, s1.exam_type, " +
            "s1.total_score_assigned as current_score_assigned, s1.total_union_rank, " +
            "CASE " +
            "WHEN (s1.total_score - s2.total_score) < -20 THEN 'MajorDecline' " +
            "WHEN (s1.total_score - s2.total_score) < -10 THEN 'Decline' " +
            "WHEN s1.class_rank > (SELECT COUNT(*) * 0.8 FROM system_exam_score s3 " +
            "WHERE s3.class_id = #{classId} AND s3.exam_id = #{currentExamId}) THEN 'LowRank' " +
            "ELSE 'Other' " +
            "END as attentionReason " +
            "FROM system_exam_score s1 " +
            "LEFT JOIN system_exam_score s2 ON s1.student_id = s2.student_id " +
            "WHERE s1.class_id = #{classId} AND s1.exam_id = #{currentExamId} " +
            "AND s2.exam_id = #{previousExamId} " +
            "AND ((s1.total_score - s2.total_score) < -10 " +
            "OR s1.class_rank > (SELECT COUNT(*) * 0.8 FROM system_exam_score s4 " +
            "WHERE s4.class_id = #{classId} AND s4.exam_id = #{currentExamId})) " +
            "ORDER BY (s1.total_score - s2.total_score) ASC")
    List<Map<String, Object>> selectBatchAttentionStudents(@Param("classId") Long classId,
                                                           @Param("currentExamId") Long currentExamId,
                                                           @Param("previousExamId") Long previousExamId);

    // ==================== Cache Support Methods ====================

    /**
     * Get basic exam information (for cache key generation)
     */
    @Select("SELECT exam_id, COUNT(*) as student_count, MAX(update_time) as last_update " +
            "FROM system_exam_score WHERE exam_id = #{examId} GROUP BY exam_id")
    Map<String, Object> selectExamCacheInfo(@Param("examId") Long examId);

    /**
     * Check if exam data has been updated (for cache invalidation)
     */
    @Select("SELECT COUNT(*) FROM system_exam_score " +
            "WHERE exam_id = #{examId} AND update_time > #{lastCacheTime}")
    Integer checkExamDataUpdated(@Param("examId") Long examId, @Param("lastCacheTime") String lastCacheTime);

    // ==================== Performance Monitoring Methods ====================

    /**
     * Get query performance statistics
     */
    @Select("SELECT " +
            "'exam_score_queries' as metric_name, " +
            "COUNT(*) as query_count, " +
            "AVG(TIMESTAMPDIFF(MICROSECOND, create_time, update_time)) as avg_response_time " +
            "FROM system_exam_score " +
            "WHERE create_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)")
    Map<String, Object> selectQueryPerformanceStats();
}